'''
Created on 17-10-2012

@author: LONG HOANG GIANG
'''
import sys, os
sys.path.append(os.path.join(os.path.dirname(__file__), '../'))
from CrawlerLib.sqlitehelper import SQLiteHelper

class SQLiteDatabase():
    
    __databaseName = ''
    db = None
    
    def __init__(self, database):
        self.__databaseName = database
        
    def open(self, delete=False):
        if os.path.isfile(self.__databaseName) and delete:
            os.unlink(self.__databaseName)
        self.db = SQLiteHelper(self.__databaseName)
        self.create_tables()
        
    def close(self):
        if self.db is not None:
            self.db.commit(); self.db.close()
            
    def convert_old_db(self):
        tables = self.db.get_list_tables()
        if 'tblChapters' in tables or 'tblDetail' in tables or 'vn_gq_udv_chapters' in tables or 'vn_gq_udv_details' in tables:
            self.db.execute("DROP TABLE IF EXISTS vn_gq_udv_category")
            self.db.execute("DROP TABLE IF EXISTS vn_gq_udv_story")
        if 'vn_gq_udv_chapters' in tables and 'vn_gq_udv_details' in tables:
            self.db.execute("ALTER TABLE vn_gq_udv_chapters RENAME TO tblChapters")
            self.db.execute("ALTER TABLE vn_gq_udv_details RENAME TO tblDetail")
        tables = self.db.get_list_tables()
        indexes = self.db.get_list_index()
        for idx in indexes:
            if 'sqlite_autoindex' in idx: continue
            self.db.execute("DROP INDEX {0}".format(idx))
        if 'tblChapters' in tables and 'tblDetail' in tables:
            self.create_tables()
            self.db.execute("INSERT INTO vn_gq_udv_story(title, detail) SELECT tblChapters.title, tblDetail.detail FROM tblChapters, tblDetail WHERE tblChapters.id = tblDetail.id")
            self.db.execute("DROP TABLE tblChapters")
            self.db.execute("DROP TABLE tblDetail")
        return
    
    def create_tables(self):
#        self.db.execute("CREATE TABLE IF NOT EXISTS vn_gq_udv_category(catId INTEGER PRIMARY KEY AUTOINCREMENT, catName VARCHAR(200), root INTEGER DEFAULT 0, UNIQUE(catName) ON CONFLICT IGNORE)")
        self.db.execute("CREATE TABLE IF NOT EXISTS vn_gq_udv_category(catId INTEGER PRIMARY KEY AUTOINCREMENT, catName VARCHAR(200), root INTEGER DEFAULT 0, kdCatName VARCHAR(200))")
        self.db.execute("CREATE TABLE IF NOT EXISTS vn_gq_udv_story(id INTEGER PRIMARY KEY AUTOINCREMENT, catId INTEGER DEFAULT 0, title VARCHAR(200), detail TEXT)")
        #self.db.execute("CREATE INDEX IF NOT EXISTS idx_category_namekd ON vn_gq_udv_category(kdCatName)")
        self.db.execute("CREATE INDEX IF NOT EXISTS idx_category_root ON vn_gq_udv_category(root)")
        self.db.execute("CREATE INDEX IF NOT EXISTS idx_detail_cid ON vn_gq_udv_story(catId)")
        
    
    